using System;
using System.Collections.Generic;
using System.Text;
using Customs.Components.KDT.GC.Info;
using Customs.Components;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using Microsoft.ApplicationBlocks.Data;
namespace Customs.Components.KDT.GC
{
    public class NguyenPhuLieu : NguyenPhuLieuInfo
    {
        public bool Load()
        {
            SqlDataReader reader = this.Select();
            if (reader.Read())
            {
                if (!reader.IsDBNull(reader.GetOrdinal("SoHopDong"))) this.SoHopDong = reader.GetString(reader.GetOrdinal("SoHopDong"));
                if (!reader.IsDBNull(reader.GetOrdinal("MaHaiQuan"))) this.MaHaiQuan = reader.GetString(reader.GetOrdinal("MaHaiQuan"));
                if (!reader.IsDBNull(reader.GetOrdinal("MaDoanhNghiep"))) this.MaDoanhNghiep = reader.GetString(reader.GetOrdinal("MaDoanhNghiep"));
                if (!reader.IsDBNull(reader.GetOrdinal("NgayKy"))) this.NgayKy = reader.GetDateTime(reader.GetOrdinal("NgayKy"));
                if (!reader.IsDBNull(reader.GetOrdinal("MaNPL"))) this._Ma = reader.GetString(reader.GetOrdinal("MaNhomSP"));
                if (!reader.IsDBNull(reader.GetOrdinal("MaHS"))) this.MaHS = reader.GetString(reader.GetOrdinal("MaHS"));
                if (!reader.IsDBNull(reader.GetOrdinal("TenNPL"))) this._Ten = reader.GetString(reader.GetOrdinal("TenNPL"));
                if (!reader.IsDBNull(reader.GetOrdinal("SoLuong"))) this._SoLuongDangKy = reader.GetDecimal(reader.GetOrdinal("SoLuong"));
                if (!reader.IsDBNull(reader.GetOrdinal("MaDVT"))) this._DVT_ID = reader.GetString(reader.GetOrdinal("MaDVT"));
                if (!reader.IsDBNull(reader.GetOrdinal("STTHang"))) this.STTHang = reader.GetInt32(reader.GetOrdinal("STTHang"));
                reader.Close();
                return true;
            }
            reader.Close();
            return false;
        }
        public void Load(NguyenPhuLieuInfo npl)
        {
            this.SoHopDong = npl.SoHopDong;
            this.SoLuongDangKy = npl.SoLuongDangKy;
            this.STTHang = npl.STTHang;
            this.NgayKy = npl.NgayKy;
            this.MaHS = npl.MaHS;
            this._Ma = npl.Ma;
            this.STTHang = npl.STTHang;
            this._MaDoanhNghiep = npl.MaDoanhNghiep;
            this._MaHaiQuan = npl.MaHaiQuan;
            this._Ten = npl.Ten;
            this._DVT_ID = npl.DVT_ID;
        }
        public SqlDataReader Select()
        {
            string connectionString = ConfigurationManager.ConnectionStrings["ConnectionString_KDT"].ConnectionString;
            StringBuilder query = new StringBuilder();
            query.Append("SELECT ");
            query.Append("So_HD AS [SoHopDong], ");
            query.Append("Ma_HQHD AS [MaHaiQuan], ");
            query.Append("DVGC AS [MaDoanhNghiep], ");
            query.Append("Ngay_Ky AS [NgayKy], ");
            query.Append("P_Code AS [MaSP], ");
            query.Append("HS_Code AS [MaHS], ");
            query.Append("Ten_NPL AS [TenNPL], ");
            query.Append("SL_DK AS [SoLuong], ");
            query.Append("Ma_DVT AS [MaDVT], ");
            query.Append("STTHang AS [STTHang] ");
            query.Append("FROM dbo.[tblDNPLHD] ");
            query.Append("WHERE ");
            query.Append("So_HD = @SoHopDong AND Ma_HQHD = @MaHaiQuan AND DVGC = @MaDoanhNghiep and ngay_ky=@NgayKy and P_Code=@MaNPL");

            SqlParameter[] parameters = new SqlParameter[5];
            parameters[0] = new SqlParameter("@SoHopDong", SqlDbType.VarChar);
            parameters[0].Value = this._SoHopDong;

            parameters[1] = new SqlParameter("@MaHaiQuan", SqlDbType.Char,6);
            parameters[1].Value = this._MaHaiQuan;

            parameters[2] = new SqlParameter("@MaDoanhNghiep", SqlDbType.VarChar);
            parameters[2].Value = this._MaDoanhNghiep;

            parameters[3] = new SqlParameter("@NgayKy", SqlDbType.DateTime);
            parameters[3].Value = this._NgayKy;

            parameters[4] = new SqlParameter("@MaNPL", SqlDbType.VarChar);
            parameters[4].Value = this._Ma;
            return SqlHelper.ExecuteReader(connectionString, CommandType.Text, query.ToString(), parameters);
        }

        //---------------------------------------------------------------------------------------------
        public int Delete(SqlTransaction transaction)
        {
            string query = string.Format("DELETE FROM tblDNPLHD WHERE So_HD = '{0}' AND Ma_HQHD = '{1}' AND DVGC = '{2}' and ngay_ky='{3}' and P_Code='{4}'", this.SoHopDong, this.MaHaiQuan, this.MaDoanhNghiep, this.NgayKy, this.Ma);
            return SqlHelper.ExecuteNonQuery(transaction, CommandType.Text, query);
        }
        public int Delete()
        {
            string connectionString = ConfigurationManager.ConnectionStrings["ConnectionString_KDT"].ConnectionString;
            string query = string.Format("DELETE FROM tblDNPLHD WHERE So_HD = '{0}' AND Ma_HQHD = '{1}' AND DVGC = '{2}' and ngay_ky='{3}' and P_Code='{4}'", this.SoHopDong, this.MaHaiQuan, this.MaDoanhNghiep, this.NgayKy, this.Ma);
            return SqlHelper.ExecuteNonQuery(connectionString, CommandType.Text, query);
        }
        //---------------------------------------------------------------------------------------------
        public void Delete(SqlTransaction transaction, NguyenPhuLieuInfoCollection collection)
        {
            NguyenPhuLieu npl= new NguyenPhuLieu();
            foreach (NguyenPhuLieuInfo itemInfo in collection)
            {
                npl.Load(itemInfo);
                npl.Delete();
            }
        }
        //---------------------------------------------------------------------------------------------
        public bool Delete(NguyenPhuLieuInfoCollection collection)
        {
            bool returnValue;
            using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString_KDT"].ConnectionString))
            {
                conn.Open();
                SqlTransaction transaction = conn.BeginTransaction();

                try
                {
                    this.Delete(transaction, collection);
                    transaction.Commit();
                    returnValue = true;
                }
                catch
                {
                    returnValue = false;
                    transaction.Rollback();
                }
                finally
                {
                    conn.Close();
                }
            }
            return returnValue;
        }

        //---------------------------------------------------------------------------------------------
        public void InsertTransaction(SqlTransaction transaction, NguyenPhuLieuInfoCollection collection)
        {
            NguyenPhuLieu npl = new NguyenPhuLieu();
            foreach (NguyenPhuLieuInfo itemInfo in collection)
            {
                npl.Load(itemInfo);
                npl.InsertTransaction(transaction);
            }
        }

        //---------------------------------------------------------------------------------------------
        public void InsertTransaction(SqlTransaction transaction)
        {
            StringBuilder query = new StringBuilder();
            query.Append("INSERT INTO tblDNPLHD ( ");
            query.Append("So_HD , ");
            query.Append("Ma_HQHD , ");
            query.Append("DVGC , ");
            query.Append("Ngay_Ky , ");
            query.Append("P_Code , ");
            query.Append("HS_Code , ");
            query.Append("Ten_NPL , ");
            query.Append("SL_DK , ");
            query.Append("Ma_DVT , ");
            query.Append("Ngay_VM , ");            
            query.Append("STTHang ) ");

            query.Append("VALUES ( ");

            query.Append("@So_HD, ");
            query.Append("@Ma_HQHD, ");
            query.Append("@DVGC, ");
            query.Append("@Ngay_Ky, ");
            query.Append("@P_Code, ");
            query.Append("@HS_Code, ");
            query.Append("@Ten_NPL, ");
            query.Append("@SL_DK, ");
            query.Append("@Ma_DVT , ");
            query.Append("@Ngay_VM , ");            
            query.Append("@STTHang )");

            SqlParameter[] parameters = new SqlParameter[11];

            parameters[0] = new SqlParameter("@So_HD", SqlDbType.VarChar);
            parameters[0].Value = this.SoHopDong;

            parameters[1] = new SqlParameter("@Ma_HQHD", SqlDbType.Char,6);
            parameters[1].Value = this.MaHaiQuan;

            parameters[2] = new SqlParameter("@DVGC", SqlDbType.VarChar);
            parameters[2].Value = this.MaDoanhNghiep;

            parameters[3] = new SqlParameter("@Ngay_Ky", SqlDbType.DateTime);
            parameters[3].Value = this.NgayKy;

            parameters[4] = new SqlParameter("@P_Code", SqlDbType.VarChar);
            parameters[4].Value = this.Ma;

            parameters[5] = new SqlParameter("@HS_Code", SqlDbType.VarChar);
            parameters[5].Value = this._MaHS;

            parameters[6] = new SqlParameter("@Ten_NPL", SqlDbType.NVarChar);
            parameters[6].Value = this._Ten;

            parameters[7] = new SqlParameter("@SL_DK", SqlDbType.Decimal);
            parameters[7].Value = this._SoLuongDangKy;

            parameters[8] = new SqlParameter("@Ma_DVT", SqlDbType.VarChar);
            parameters[8].Value = this._DVT_ID;

            parameters[9] = new SqlParameter("@Ngay_VM", SqlDbType.DateTime);
            parameters[9].Value = DateTime.Today;
           
            parameters[10] = new SqlParameter("@STTHang", SqlDbType.Int);
            parameters[10].Value = this.STTHang;

            SqlHelper.ExecuteNonQuery(transaction, CommandType.Text, query.ToString(), parameters);
        }

        public static NguyenPhuLieuInfoCollection selectNguyenPhuLieuInfoCollectionByHopDong(string mahaiquan, string madoanhnghiep, string sohopdong, DateTime ngayky)
        {
            string connectionString = ConfigurationManager.ConnectionStrings["ConnectionString_KDT"].ConnectionString;
            StringBuilder query = new StringBuilder();
            query.Append("SELECT ");
            query.Append("So_HD AS [SoHopDong], ");
            query.Append("Ma_HQHD AS [MaHaiQuan], ");
            query.Append("DVGC AS [MaDoanhNghiep], ");
            query.Append("Ngay_Ky AS [NgayKy], ");
            query.Append("P_Code AS [MaNPL], ");
            query.Append("HS_Code AS [MaHS], ");
            query.Append("Ten_NPL AS [TenNPL], ");
            query.Append("SL_DK AS [SoLuong], ");
            query.Append("Ma_DVT AS [MaDVT], ");
            query.Append("STTHang AS [STTHang] ");
            query.Append("FROM dbo.[tblDNPLHD] ");
            query.Append("WHERE ");
            query.Append("So_HD = @SoHopDong AND Ma_HQHD = @MaHaiQuan AND DVGC = @MaDoanhNghiep and ngay_ky=@NgayKy");

            SqlParameter[] parameters = new SqlParameter[4];
            parameters[0] = new SqlParameter("@SoHopDong", SqlDbType.VarChar);
            parameters[0].Value = sohopdong;

            parameters[1] = new SqlParameter("@MaHaiQuan", SqlDbType.Char, 6);
            parameters[1].Value = mahaiquan;

            parameters[2] = new SqlParameter("@MaDoanhNghiep", SqlDbType.VarChar);
            parameters[2].Value = madoanhnghiep;

            parameters[3] = new SqlParameter("@NgayKy", SqlDbType.DateTime);
            parameters[3].Value = ngayky;

            IDataReader reader= SqlHelper.ExecuteReader(connectionString, CommandType.Text, query.ToString(), parameters);
            NguyenPhuLieuInfoCollection nplCollection = new NguyenPhuLieuInfoCollection();
            while (reader.Read())
            {
                NguyenPhuLieuInfo nplInfo = new NguyenPhuLieuInfo();
                if (!reader.IsDBNull(reader.GetOrdinal("MaHaiQuan"))) nplInfo.MaHaiQuan = reader.GetString(reader.GetOrdinal("MaHaiQuan"));
                if (!reader.IsDBNull(reader.GetOrdinal("MaDoanhNghiep"))) nplInfo.MaDoanhNghiep = reader.GetString(reader.GetOrdinal("MaDoanhNghiep"));
                if (!reader.IsDBNull(reader.GetOrdinal("NgayKy"))) nplInfo.NgayKy = reader.GetDateTime(reader.GetOrdinal("NgayKy"));
                if (!reader.IsDBNull(reader.GetOrdinal("MaNPL"))) nplInfo.Ma = reader.GetString(reader.GetOrdinal("MaNPL"));
                if (!reader.IsDBNull(reader.GetOrdinal("MaHS"))) nplInfo.MaHS = reader.GetString(reader.GetOrdinal("MaHS"));
                if (!reader.IsDBNull(reader.GetOrdinal("TenNPL"))) nplInfo.Ten = reader.GetString(reader.GetOrdinal("TenNPL"));
                if (!reader.IsDBNull(reader.GetOrdinal("SoLuong"))) nplInfo.SoLuongDangKy = reader.GetDecimal(reader.GetOrdinal("SoLuong"));
                if (!reader.IsDBNull(reader.GetOrdinal("MaDVT"))) nplInfo.DVT_ID = reader.GetString(reader.GetOrdinal("MaDVT"));
                if (!reader.IsDBNull(reader.GetOrdinal("STTHang"))) nplInfo.STTHang = reader.GetInt32(reader.GetOrdinal("STTHang"));
                if (!reader.IsDBNull(reader.GetOrdinal("SoHopDong"))) nplInfo.SoHopDong = reader.GetString(reader.GetOrdinal("SoHopDong"));
                nplCollection.Add(nplInfo);
            }
            reader.Close();
            return nplCollection;
        }
  
    }
}
